<?php
require_once dirname(__FILE__) . '/DbUtils.class.php';
require_once dirname(__FILE__) . '/metadata.php';
require_once dirname(__FILE__) . '/config.php';
require_once dirname(__FILE__) . '/includes/MiscUtils.class.php';
require_once dirname(__FILE__) . '/libs/phpMailer/class.phpmailer.php';

/**
 * @param $myPdo
 * @param $class id, teacher_name, atr2:paper_name, attr8:name, 
 * @param $start
 * @param $is_download
 */
function class2Xls($myPdo,$class,$start,$is_download) {
  $cond_vals = new stdClass();
  $cond_vals->c = 't.class_id = :v1 AND ((t.enrolled_semester_id != 0 AND (t.status_id = :v2 OR (t.status_id = :v3 AND t.wd_date > :v4))) OR ((t.start <= :v4 AND t.end >=:v4) AND t.enrolled_semester_id = 0))';
  $cond_vals->v = array(':v1' => $class->id, ':v2' => STATUS_CLASS_ENROLLED, ':v3' => STATUS_KEEP_RECORDS, ':v4' => $start);
  $opts = new stdClass();
  $opts->select_expr = 't.enrolled_program_id, t.enrolled_semester_id, t1.*';
  $opts->join_expr = 'LEFT JOIN ' . TABLE_NAME_PREFIX_PLUGIN . 'student_students as t1 ON t1.id = t.student_id';
  $students = DbUtils::get($myPdo, TABLE_NAME_PREFIX_PLUGIN . 'student_enrolled_classes', $cond_vals, 't1.given_name', 'ASC', NULL, NULL, $opts)->d;
  
  $end = date('Y-m-d', strtotime($start)+6*86400);
  foreach ($students as $student) {
    $cond_vals = new stdClass();
    $cond_vals->c = 't.student_id = :v1 AND ((t.start >= :v2 AND t.end >= :v3 AND t.start < :v3) OR (t.start <= :v2 AND t.end < :v3 AND t.end > :v2) OR (t.start <= :v2 AND t.end >= :v3 ))';
    $cond_vals->v = array(':v1' => $student->id, ':v2' => $start, ':v3' => $end);
    $student->_notes = DbUtils::get($myPdo, TABLE_NAME_PREFIX_PLUGIN . 'student_notes', $cond_vals, NULL, NULL, NULL, NULL, NULL)->d;
  }

  $cond_vals = new stdClass();
  $cond_vals->c = 't.attr1 = :v1 AND attr6 = 0';
  $cond_vals->v = array(':v1' => $class->id);
  $sessions = DbUtils::get($myPdo, TABLE_NAME_PREFIX_PLUGIN . 'semester_class_sessions', $cond_vals, 't.date1', 'ASC', NULL, NULL, NULL)->d;


  $days = array();
  for ($i = strtotime($start), $il = (strtotime($start)+6*86400); $i <= $il; $i += 86400) {
    $days[date('w', $i)] = date('Y-m-d', $i);
  }

  foreach ($sessions as &$session) {
    $session->day = MiscUtils::getDay(date('w', strtotime($session->date1)));
    $session->time1 = date('H:i', strtotime($session->date1));
    $session->time2 = date('H:i', strtotime($session->date2));
    $session->actual_date = $days[date('w', strtotime($session->date1))];
  }

  date_default_timezone_set('Pacific/Auckland');

  /** PHPExcel */
  require_once dirname(__FILE__) . '/libs/phpexcel/PHPExcel.php';

  // Create new PHPExcel object
  $objPHPExcel = new PHPExcel();
  
  $path = sys_get_temp_dir();
  $file = 'ATT_' . $class->teacher_name . '_' . $class->attr2 . ((trim($class->attr8) == '') ? '' : ' ' . $class->attr8 ). '_' . $start . '_' . $end . '.xlsx';

  $info = new stdClass();
  $info->class_id = $class->id;
  $info->secret = SECRET;
  $info->sessions = count($sessions);
  $info->students = count($students);
  $info->start = $start;
  $info->end = $end;
  // Set properties
  $objPHPExcel->getProperties()->setCreator("$class->teacher_name")
                 ->setLastModifiedBy("$class->teacher_name")
                 ->setTitle($class->attr2 . ((trim($class->attr8) == '') ? '' : ' ' . $class->attr8 ))
                 ->setSubject($start . ' to ' . $end)
                 ->setDescription(json_encode($info))
                 ->setKeywords('')
                 ->setCategory("Office 2007 XLSX Document");
 
  // Rename sheet
  $objPHPExcel->getActiveSheet()->setTitle("$start $end");
  
  $horizontal = 67;
  $vertical = 4;
  
  $objPHPExcel->getActiveSheet()->mergeCells('A1:' . chr(count($sessions) + $horizontal) . '1');
  $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A1', 'If a student is absent, mark "a". If late, mark "l" DO NOT edit ANYTHING else');
  
  foreach ($sessions as $idx => &$session) {
    $objPHPExcel->setActiveSheetIndex(0)->setCellValue(chr($idx + $horizontal) . '2', $session->id . '#' . date('Y-m-d', strtotime($session->actual_date)));
    $objPHPExcel->setActiveSheetIndex(0)->setCellValue(chr($idx + $horizontal) . '3', $session->day . ' ' . $session->time1 . '-' . $session->time2);
    $objPHPExcel->getActiveSheet()->getColumnDimension(chr($idx + $horizontal))->setWidth(15);
  }

  foreach ($students as $idx => &$student) {
    $objPHPExcel->setActiveSheetIndex(0)->setCellValue(chr($horizontal - 2) . ($idx + $vertical), $student->id . '#' . $student->id_number);
    $objPHPExcel->getActiveSheet()->getColumnDimension(chr($horizontal - 2))->setWidth(15);
    $objPHPExcel->setActiveSheetIndex(0)->setCellValue(chr($horizontal - 1) . ($idx + $vertical), $student->given_name . ' ' . $student->family_name . ' (' . $student->preferred_name . ')');
    $objPHPExcel->getActiveSheet()->getColumnDimension(chr($horizontal - 1))->setWidth(20);
    foreach ($student->_notes as $note) {
      foreach ($sessions as $idx2 => &$session) {
        if (strtotime($session->actual_date) >= strtotime($note->start) && strtotime($session->actual_date) <= strtotime($note->end)) {
          $objPHPExcel->setActiveSheetIndex(0)->setCellValue(chr($idx2 + $horizontal) . ($idx + $vertical), $note->reason_name);
        }
      }
    }
  }

  // Set active sheet index to the first sheet, so Excel opens this as the first sheet
  $objPHPExcel->setActiveSheetIndex(0);

  $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
  if (!$is_download){
    $objWriter->save($path . $file);
    return $path . $file;
  } else {
    header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    header('Content-Disposition: attachment;filename="' . $file . '"');
    header('Cache-Control: max-age=0');
    $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
    $objWriter->save('php://output');
    return true;
  }
}

function xls2Email($path,$to){
  $mailer = new PHPMailer();
  $mailer->CharSet = "UTF-8";
  
  $mailer->IsSMTP();
  $mailer->Host = SMTP_HOST;
  
  $mailer->SMTPAuth = SMTP_AUTH;
  $mailer->Username = SMTP_USER_ATDN;
  $mailer->Password = SMTP_PASS_ATDN;
  $mailer->From = SMTP_USER_ATDN;
  $mailer->FromName = 'NZIOS';
  $mailer->Body = 'Please find the attached excel file';
  $mailer->Subject = basename($path);

  $mailer->isHTML(false);
  
  $mailer->AddAddress($to, '');
  
  $mailer->AddAttachment($path, basename($path));
  
  if (!$mailer->Send()) {
    die('Sent Failed');
  }
  return true;
}

function getClasses($myPdo) {
  $start = date('Y-m-d', strtotime('next Monday', time()));
  $end = date('Y-m-d', strtotime('next Monday', time())+6*86400);
  
  $cond_vals = new stdClass();
  $cond_vals->c = 't.path1 = 0 AND t.attr10 = :v1 AND ((t1.date1 >= :v2 AND t1.date2 >= :v3 AND t1.date1 < :v3) OR (t1.date1 <= :v2 AND t1.date2 < :v3 AND t1.date2 > :v2) OR (t1.date1 <= :v2 AND t1.date2 >= :v3 ) OR (t.attr5 = 0))';
  $cond_vals->v = array(':v1' => 0 , ':v2' => $start , ':v3' => $end);
  $opts = new stdClass();
  $opts->select_expr = 't.*, t2.attr3 AS teacher_name, t2.attr4 AS teacher_email';
  $opts->join_expr = "LEFT JOIN " . TABLE_NAME_PREFIX_PLUGIN . "semester_semesters as t1 ON t.attr5 = t1.id AND t1.attr2 = 0 LEFT JOIN " . TABLE_NAME_PREFIX_PLUGIN . "users_users as t2 ON t2.id = t.attr6";
  $classes = DbUtils::get($myPdo, TABLE_NAME_PREFIX_PLUGIN . 'semester_classes', $cond_vals, NULL, NULL, NULL, NULL, $opts)->d;
  return $classes;
}

?>